实战oracle数据库编程

实战oracle数据库编程

登陆：oracle库
用户名：scott
密 码：tiger

用户名：system
密码： manager
登陆用户命令：connect liuran/hello

建立用户： create user liuran identified by hello;

查看数据字典里的所有当前用户可用的表：select * from tab;

查看所有数据字典里的表：select * from drct;

查看当前用户：select user from dual;

给绘画权限(登陆数据库)：grant create session to liuran;--必须要有，否则不能登陆数据库

给建表权限：grant create table to liuran;
grant unlimited tablespace to liuran;
查询表授权：grant select on student to system;

建表: create table student(
name varchar2(30),
age number(2),
home varchar2(18));

在DOS下可进行后退编辑:
c:>sqlplus
请输入用户名:system
请输入密码:manager


查询表： select * from student;

查询其他用户的表：select * from system.student;

查询表的结构：desc student;

复制表格： create table student2 as select * from student;

复制表格结构： create table student3 as select * from student where 1=2;

插入数据： insert into student values('liuran',20,'beijingchaoyang');

更新数据： update student set home='beijing' where age=20;
update student set home='beijing'；(改所有的记录)

删除数据： delete student where age=44;

显示3： select 3 from dual;

回滚： rollback;

提交： commit;

更改日期格式： alter session set nls_date_format='yyyy * mm * dd';
alter session set nls_date_format='yyyy "年" mm"月"dd"日"';

显示当前时间：select sysdate from dual;

TO_CHAR()函数：select to_char(sysdate,'yyyy"年"mm"月"dd"日"')
from dual;

行级锁:

blocking

SELECT...FOR UPDATE

SELECT * FROM dept WHERE deptno = 20 FOR UPDATE;

SELECT * FROM dept WHERE deptno = 20 FOR UPDATE OF dname;

SELECT * FROM dept WHERE deptno = 20 FOR UPDATE WAIT 10;

表级锁:

(1)共享锁:
conn scott/tiger;
LOCK TABLE dept IN SHARE MODE;
conn system/manager;
select * from scott.dept;
(2)共享更新锁:
conn scott/tiger;
LOCK TABLE dept IN SHARE UPDATE MODE;
conn system/manager;
select * from scott.dept;
update scott.dept set loc='aa' where deptno=20

(3)排他锁:
conn scott/tiger;
LOCK TABLE dept IN EXCLUSIVE MODE;
conn system/manager;
(4)LOCK TABLE dept IN EXCLUSIVE MODE NOWAIT;



范围分区:根据列值的范围将行映射到分区
CREATE TABLE census (
id varchar2(18) primary key,
name varchar2(30),
age number(3))
partition by range(age)
(partition p1 values less than (30),
partition p2 values less than (60),
partition p3 values less than (maxvalue));

散列分区:
在分区列上使用 hash 函数
是比范围分区更好的选择
如果不知道将有多少数据映射到的范围，散列分区非常有用
分区的数目应是 2 的幂
可以对散列分区进行命名，并将其存储在特定的表空间中

create table student (
name varchar2(30),
age number(2),
score number(2))
partition by hash(age)
(partition p1, partition p2, partition p3);
insert into student values('aa',10,85);
insert into student values('bb',12,80);
insert into student values('cc',15,82);
insert into student values('dd',14,83);
insert into student values('aaa',42,83);
insert into student values('ee',32,82);
insert into student values('ff',32,82);
insert into student values('ag',22,82);
insert into student values('ss',12,84);
insert into student values('ew',27,90);

复合分区:
使用范围分区方法对数据进行分区，并在每一个分区内使用散列分区方法将其划分为子分区
允许命名子分区
允许将子分区存储在特定的表空间
兼具范围分区和散列分区的优点
create table people (
name varchar2(30),
age number(3))
partition by range(age)
subpartition by hash(name)
(partition p1 values less than (30)
(subpartition sp1, subpartition sp2, subpartition sp3),
partition p2 values less than (60)
(subpartition sp4, subpartition sp5),
partition p3 values less than (maxvalue)
(subpartition sp6, subpartition sp7)
);

列表分区:
基于 PARTITION BY LIST 中的分区键对表进行分区
明确地控制行到分区的映射
分区键的离散值
不同于范围分区和散列分区
create table stud (
name varchar2(30),
age number(2),
district varchar2(20))--区域
partition by list(district)
(
partition beijing_sanhuan values('西城区', '东城区', '崇文区', '宣武区'),
partition beijing_bahuan values('海淀区', '朝阳区', '丰台区')
)
insert into stud values('aa',12,'西城区');
insert into stud values('ba',12,'东城区');
insert into stud values('ea',12,'东城区');
insert into stud values('ga',12,'西城区');
insert into stud values('a',12,'西城区');
insert into stud values('ssaa',12,'西城区');
insert into stud values('aa',12,'崇文区');
insert into stud values('eee',12,'崇文区');
insert into stud values('aa',12,'海淀区');
insert into stud values('4a',12,'海淀区');
insert into stud values('8a',12,'海淀区');
insert into stud values('eea',12,'海淀区');
insert into stud values('eeea',12,'朝阳区');
insert into stud values('fff',12,'朝阳区');

select * from stud partition(beijing_sanhuan);

select * from stud partition(beijing_bahuan);
/
维护分区：
各种分区维护操作如下：
添加分区
移动
删除
结合
截断
拆分
合并
交换表分区
/

添加分区:
create table stud2 (
name varchar2(30),
age number(2))
partition by range(age)
(partition p1 values less than (21),
partition p2 values less than (25),
partition p3 values less than (28));

alter table stud2
add partition p4 values less than(35);

删除分区:

alter table stud2 drop partition p2;

拆分分区:split--分离
alter table stud2 SPLIT PARTITION p3 at(25)
INTO (partition p31, partition p32);

合并分区:MERGE--合并
ALTER TABLE stud2 MERGE PARTITIONS p31, p32 INTO PARTITION p3;

更多更多技术文章：请关注：北京尚脑软件测试